package com.edu.internship.busi.service.impl;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.edu.internship.busi.dto.StudentDTO;
import com.edu.internship.busi.entity.Student;
import com.edu.internship.busi.mapper.StudentMapper;
import com.edu.internship.busi.service.IStudentService;
import com.edu.internship.util.BeanExUtils;
import com.edu.internship.util.ExcelUtils;
import com.edu.internship.util.excel.ExcelElement;
import com.google.common.collect.Lists;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;
import static org.apache.poi.ss.usermodel.CellType.STRING;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;


/**
 * code is far away from bug with the animal protecting
 *
 * @author longjihao
 * @description <p>Student 的ServiceImpl</p>
 * @date 2020-08-02
 */
@Service
public class StudentServiceImpl extends ServiceImpl<StudentMapper, Student> implements IStudentService {

    @Autowired
    StudentMapper studentMapper;

    @Override
    public void createStudent(StudentDTO dto) {
        Student student = BeanExUtils.convertTo(dto, Student::new);
        super.save(student);
    }

    @Override
    public void updateStudent(StudentDTO dto) {
        Student student = BeanExUtils.convertTo(dto, Student::new);
        super.updateById(student);
    }

    @Override
    public void removeStudent(List<String> id) {
        super.removeByIds(id);
    }

    @Override
    public List<Student> selectByStuId(String id) {
        LambdaQueryWrapper<Student> qw = Wrappers.lambdaQuery();
        qw.eq(Student::getId, id);
        return super.list(qw);
    }

    @Override
    public void deleteStudentByStuId(String id) {
        LambdaQueryWrapper<Student> qw = Wrappers.lambdaQuery();
        qw.eq(Student::getId, id);
        super.remove(qw);
    }

    @Override
    public void exportTest(HttpServletResponse response, String excelTitle) throws IOException {
        List<ExcelElement> elements = Lists.newArrayList();
        elements.add(new ExcelElement("主键", "id"));
        elements.add(new ExcelElement("学生姓名", "studentName"));
        elements.add(new ExcelElement("学号", "studentNumber"));
        elements.add(new ExcelElement("班级id", "classId"));
        elements.add(new ExcelElement("学生性别", "studentSex"));
        QueryWrapper queryWrapper = new QueryWrapper();
        List<Student> studentList = studentMapper.selectList(queryWrapper);
        ExcelUtils.normalExport(excelTitle, studentList, elements, response);
    }


    @Override
    public int addStudentExcelFileToDatabase(MultipartFile file)throws Exception{
//        List<ExcelElement> elements = Lists.newArrayList();
//        elements.add(new ExcelElement("id"));
//        elements.add(new ExcelElement("studentName"));
//        elements.add(new ExcelElement( "studentNumber"));
//        elements.add(new ExcelElement("classId"));
//        elements.add(new ExcelElement("studentSex"));
//        List<StudentDTO> students = ExcelUtils.readExcelData(file.getInputStream(), elements, StudentDTO.class);
//        List<Student> students1 = BeanExUtils.convertListTo(students, Student::new);
//        this.saveBatch(students1);

//        List<Student> students1 = BeanExUtils.convertListTo(students, Student::new, (s, t) -> {
//            if (s.getStudentSex().equals("nan")) {
//                t.setStudentSex("1");
//            }
//        });

        int result = 0;
            // 创建StudentList
            List<Student> studentList = new ArrayList<>();
            // 获取上传的文件名
            String fileName = file.getOriginalFilename();
           // 获取上传文件后缀
            String suffix = fileName.substring(fileName.lastIndexOf(".")+1);

            System.out.println("文件后缀为：" + suffix);
            // 输入流读取文件
            InputStream inputStream = file.getInputStream();
            // 1. 新建工作簿
            Workbook workbook = null;

            if(suffix.equals("xlsx")){
                // Excel2007版本及以后 用XSSF(即Poi-ooxml)处理
                workbook = new XSSFWorkbook(inputStream);
            } else {
                // Excel2003版本 用HSSF(即Poi)处理
                workbook = new HSSFWorkbook(inputStream);
            }
                // 2. 获取当前工作表
            Sheet sheet = workbook.getSheetAt(0);

            if(sheet != null){
                // 3. 从第二行开始遍历工作表的每一行
                for (int i = 1; i <= sheet.getLastRowNum(); i++){
                    Row row = sheet.getRow(i);
                    if (row != null){
                        List<String> list = new ArrayList<>();
                        for (Cell cell : row){
                            // 4. 遍历每一个单元格
                            if (cell != null) {
                                // 将单元格值转化为字符串
                                //cell.setCellType(Cell.CELL_TYPE_STRING); 已过时
                                cell.setCellType(STRING);
                                // 获取字符串形式的值
                                String cellValue = cell.getStringCellValue();
                                // 将值加入数组
                                list.add(cellValue);
                            }
                        }

                        // 值的转化
                        String id = list.get(0);
                        String studentName = list.get(1);
                        String studentNumber = list.get(2);
                        String classId = list.get(3);
                        String studentSex = list.get(4);

                        // 构造一个账单对象，并将从个单元格获取的值赋给它
                        Student student = new Student(id,studentName,studentNumber,classId,studentSex);
                        // 将新的一条账单加入studentList
                        studentList.add(student);
                    }
                }

                for (Student student: studentList){
                    // 将每一条账单插入数据库
                    result = studentMapper.addStudentExcelFileToDatabase(student);
                }
                
            }
            return result;
    }


}
